-- links a document into a folder using the hierarchy.  The FolderHierarchyKey points to the parent folder
CREATE PROCEDURE [dbo].[asi_DocumentLinkDocumentOut]
   @folderHierarchyKey uniqueidentifier,
   @documentVersionKey uniqueidentifier,
   @hierarchyKey uniqueidentifier OUTPUT
AS
DECLARE
   @sortOrder int,
   @documentTypeCode nvarchar(3),
   @documentName nvarchar(100),
   @rootHierarchyKey uniqueidentifier,
   @parentFolderSortOrder int,
   @parentFolderDepth int
BEGIN
   -- first check to see if such a link already exists, if not, then build it
   SELECT @hierarchyKey = HierarchyKey
     FROM Hierarchy
    WHERE ParentHierarchyKey = @folderHierarchyKey
      AND UniformKey = @documentVersionKey

   IF @hierarchyKey IS NULL
   BEGIN
      SET @sortOrder = null
      SET @hierarchyKey = NewID()

      -- see if the doc to be linked is a folder.  If so, it needs a new sort order
      SELECT TOP 1 @documentTypeCode = DocumentTypeCode,
             @documentName = DocumentName
        FROM DocumentMain
       WHERE DocumentVersionKey = @documentVersionKey
       ORDER BY CreatedOn DESC

      IF @documentTypeCode IN ('FOL','CFL','NAV')
      BEGIN
         SELECT @rootHierarchyKey = RootHierarchyKey,
                @parentFolderSortOrder = SortOrder,
                @parentFolderDepth = Depth
           FROM Hierarchy
          WHERE HierarchyKey = @folderHierarchyKey

          exec asi_HierarchyGetFolderSortOut @folderHierarchyKey, @rootHierarchyKey, @parentFolderSortOrder, @parentFolderDepth, @documentName, @sortOrder OUT
      END

      INSERT INTO Hierarchy (
             HierarchyKey,
             RootHierarchyKey,
             ParentHierarchyKey,
             SortOrder,
             Depth,
             UniformType,
             UniformKey,
             IsChildAMemberOfParent)
      SELECT @hierarchyKey,
             RootHierarchyKey,
             HierarchyKey,
             ISNULL(@sortOrder,SortOrder),
             Depth + 1,
             UniformType,
             @documentVersionKey,
             IsChildAMemberOfParent
        FROM Hierarchy
       WHERE HierarchyKey = @folderHierarchyKey

      IF @@ROWCOUNT = 0
         SET @hierarchyKey = NULL
   END
END

GO